<?php
/**
 * Report : Inquiry By Type and Customers
 * 
 * 
 */



use Fox\Html\DropList\Radio;
use Fox\Html\CheckBox;
use Fox\Html\Block;
use Fox\SalesDistribution\Sales\Partner\Role;
use Fox\SalesDistribution\Sales\Inquiry;
use Doctrine\DBAL\Types\Type;

use Fox\Grid\FieldCat\Field;
use Fox\Html\Input;
use Fox\Html\Screen;
$screen = new Screen('S','execute');
$screen->mainTitle();

$block = new Fox\Html\Block("gCriteria",Msg(Fox::MSGCL,29));
$block->add(new Input("period",true,time()));
$block->add(new CheckBox("showym1",false),2);
$block->add(new CheckBox("show_detail",false));
$block->add(new Radio("output_type",true,true,1))->addAllOption();
$inputAgency = Xview::instance()->component("reports","agencies");
$block->add($inputAgency);
;
$screen->add($block);
if($screen->submitted())
{
	$inputs = $screen->get();
	if(!$screen->errors())
	{
		$inputs['last_date_comparison'] = "P1Y";
		$total = array();
		// Manage Selection Periods
		$selectedDate = new DateTime($inputs['period']);
		$endDate = new DateTime($selectedDate->format('Y-m')."-01");
		$endDate->add(new DateInterval("P1M"));
		$startDate = new DateTime($selectedDate->format('Y')."-01-01");

		// Build the interval for month n year n
		$firstDay = new DateTime($selectedDate->format("Y")."-01-01");
		
		$lastDay = new DateTime($selectedDate->format("Y-m")."-01");
		$lastDay->add(new DateInterval("P1M"));
		
		// Init Grid
		$grid = new Fox\Grid("gReport");
		$grid->fieldcat(array(array("inquiry_type","type"),
								"customername",
								array("amount","amount_n"),
								array("rate","amount_rate_n"),
								array("number","number_n"),
								array("rate","rate_n"),

								array("amount","amount_nm1"),
								array("rate","amount_rate_nm1"),
								array("number","number_nm1"),
								array("rate","rate_nm1")));
		$grid->setXlsDownload();
		$grid->setTotal();
		
		$grid->fieldcat()->field("customername")->SetClass("separator-right");
		$grid->fieldcat()->field("amount_rate_n")->SetClass("separator-right separator-dashed-left")
							->formater()->displayZeroValue(false);
		$grid->fieldcat()->field("rate_n")->SetClass("separator-right separator-dashed-left")
							->formater()->displayZeroValue(false);
		$grid->fieldcat()->field("amount_rate_nm1")->SetClass("separator-right separator-dashed-left")
							->formater()->displayZeroValue(false);
		$grid->fieldcat()->field("rate_nm1")->SetClass("separator-dashed-left")
							->formater()->displayZeroValue(false);
		$grid->fieldcat()->setFieldAttr(array("rate_n","rate_nm1","amount_rate_nm1","amount_rate_n"),"dtype","rate");

		$data = array();
		// Label Grouping
		$fieldGroup1 = new Field("label1");
		$label = Fox\Ddic\DataElement\Text::label("month",$selectedDate->format("m"))." - ".$selectedDate->format("Y");
		$labelY = $label;
		$fieldGroup1->setClass("gd-col-group separator-right separator-left")->label($label);
		$grid->fieldcat()->buildSubTitleByGrouping(array("amount_n","amount_rate_n","number_n","rate_n"),$fieldGroup1);
		$grid->fieldcat()->field("type")
				//		->SetClass("separator-right")
						->setNoWrap()
						->width(4);
		// Load Types
		$finder = new Fox\SalesDistribution\Sales\Inquiry\Type();
		$types = $finder->find(null,null,null,null,true);
		// Load Data
		$query = \Fox\Dao::infos(Inquiry::className())->handle()->createQueryBuilder();
		$query->select('t.inquiryid','t.type','t.customerid','cu.name1','SUM(it.total1) AS total1','SUM(it.amount) AS final')
			   ->from('inquiries','t')
			   ->innerJoin('t', 'inquiry_partners', 'pa','t.inquiryid = pa.inquiryid')
			   ->innerJoin('t', 'inquiry_items', 'it','t.inquiryid = it.inquiryid')
			   ->innerJoin('t', 'materials', 'ma','it.materialid = ma.materialid')
			   ->innerJoin('t', 'customers', 'cu','cu.customerid = t.customerid')
			   ->groupBy('t.inquiryid','t.type','t.customerid','cu.name1')
			   ->orderBy('t.type')
			   ->addOrderBy('cu.name1');

		//
		$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",array($firstDay,$lastDay),array(Type::DATE,Type::DATE));
		$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? AND pa.role = ? AND pa.loekz = ? AND it.loekz = ?",array(0,Role::RESPONSIBLE_AGENCY,0,0));
		if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
			$fragments[] = User::current()->inquiryFragments();
		$fragments[] = $block->item("op_entity")->fragments("pa.customerid");
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);

		$total_amount_n = $total_n = 0;
		foreach($result as $item)
		{
			$count = 0;
			
			if(!isset($inqId[$item['inquiryid']]))
			{
				$inqId[$item['inquiryid']] = true;
				$count = 1;
			
			}
			$key = $item['type'].$item['customerid'];
			if($item['type'] == Inquiry::TYPE_PRIVATE)
			{
				$key = $item['type'];
				$item['name1'] = null;
			}
			$amount =  $item['total1'];
			
			$type = (int) $item['type'];
			if(isset($types[$type]))
			{
				$data[$key]['type'] = $types[$type]->label();
			}
			else
			{
				$data[$key]['type'] = '';
			}
			$data[$key]['customername'] = $item['name1'];
			if(isset($data[$key]['amount_n'] ))
			{
				$data[$key]['amount_n'] += $amount;
				$data[$key]['number_n'] += $count;
			}
			else
			{
				$data[$key]['amount_n'] = $amount;
				$data[$key]['number_n'] = $count;
			}
			
			$total_n += 1;
			$total_amount_n += $amount;
			// Total Lines
			if(isset($total[$item['type']]))
			{
				$total[$item['type']]['amount_n'] += $amount;
				$total[$item['type']]['number_n'] += $count;
			}
			else
			{
				
				$total[$item['type']]['type'] = $types[$type]->label();
				$total[$item['type']]['amount_n'] = $amount;
				$total[$item['type']]['number_n'] = $count;
			}
		}
		$total_amount_nm1 = $total_nm1 = 0;
		if($inputs['showym1'])
		{
			$firstDayLastYear = new DateTime($firstDay->format("Y-m-d"));
			$firstDayLastYear->sub(new DateInterval($inputs['last_date_comparison']));
			$lastDayLastYear = new DateTime($lastDay->format("Y-m-d"));
			$lastDayLastYear->sub(new DateInterval($inputs['last_date_comparison']));
			$mtnNum = $selectedDate->format("m");
			$selectedDate->sub(new DateInterval($inputs['last_date_comparison']));	
			$label = Fox\Ddic\DataElement\Text::label("month",$mtnNum)." - ".$selectedDate->format("Y");
			$labelYm1 = $label;
			$fieldGroup3 = new Field("label3");
			$fieldGroup3->setClass("gd-col-group separator-right separator-left")
						->label($label);
			
			$grid->fieldcat()->buildSubTitleByGrouping(array("amount_nm1","amount_rate_nm1","number_nm1","rate_nm1"),$fieldGroup3);
	
			$fragments = null;
			$fragments[] = new Fox\Dao\SqlFragment("start_date >= ? AND start_date < ? ",array($firstDayLastYear,$lastDayLastYear),array(Type::DATE,Type::DATE));
			$fragments[] = new Fox\Dao\SqlFragment("t.loekz = ? AND pa.role = ? AND pa.loekz = ? AND it.loekz = ?",array(0,Role::RESPONSIBLE_AGENCY,0,0));
			if(!Fox\Authorization\Manager::check(User::AUTH_VIEW_ALL))
				$fragments[] = User::current()->inquiryFragments();
			$fragments[] = $block->item("op_entity")->fragments("pa.customerid");
			$fragment = Fox\Dao\SqlFragment::combine($fragments);
			$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
	
			
			foreach($result as $item)
			{
				if(!isset($inqId[$item['inquiryid']]))
				{
					$inqId[$item['inquiryid']] = true;
					$count = 1;
				
				}
				$key = $item['type'].$item['customerid'];
				if($item['type'] == Inquiry::TYPE_PRIVATE)
				{
				
					$key = $item['type'];
					$item['name1'] = null;
				}
				$amount =  $item['total1'];
				if(isset($types[$item['type']]))
				{
					$data[$key]['type'] = $types[$item['type']]->label();
				}
				else
				{
					$data[$key]['type'] = '';
				}
				$data[$key]['customername'] = $item['name1'];
				if(isset($data[$key]['amount_nm1'] ))
				{
					$data[$key]['amount_nm1'] += $amount;
					$data[$key]['number_nm1'] += $count;
				}
				else
				{
					$data[$key]['amount_nm1'] = $amount;
					$data[$key]['number_nm1'] = $count;
				}
			
				$total_amount_nm1 += $amount;
				$total_nm1 += 1;
				// Total Lines
				if(isset($total[$item['type']]['amount_nm1']))
				{
					$total[$item['type']]['amount_nm1'] += $amount;
					$total[$item['type']]['number_nm1'] += $count;
				}
				else
				{
					$total[$item['type']]['amount_nm1'] = $amount;
					$total[$item['type']]['number_nm1'] = $count;
				}
			}
		}
		// Buid % lines
		if(!$inputs['show_detail'])
		{
			$data = $total;
		}
		else 
		{
			$grid->fieldcat()->setSubTotalFields(array("type"));
		}
		
		foreach($data as $key => $item)
		{
			if($total_n > 0 && isset($item['number_n']))
				$data[$key]['rate_n'] = $item['number_n'] / $total_n *100;
			if($total_nm1 > 0  && isset($item['number_nm1']))
				$data[$key]['rate_nm1'] = $item['number_nm1'] / $total_nm1 *100;
			if($total_amount_nm1 > 0 && isset($item['amount_nm1']))
				$data[$key]['amount_rate_nm1'] = $item['amount_nm1'] / $total_amount_nm1 *100;
			if($total_amount_n > 0 && isset($item['amount_n']))
				$data[$key]['amount_rate_n'] = $item['amount_n'] / $total_amount_n *100;
		}

		if(!$inputs['showym1'])
		{
			$grid->fieldcat()
					->setFieldAttr(array("amount_nm1",
										"amount_rate_nm1",
										"number_nm1",
										"rate_nm1"),"noOutput",true);

		}
		
		$output = null;
		if($inputs['output_type'] == 1 || $inputs['output_type'] == Radio::ALL)
		{
			$grid->initialize($data);
			$output .= $grid->Display();
		}
		if( ($inputs['output_type'] == 0 || $inputs['output_type'] == Radio::ALL) && empty($inputs['show_detail']) )
		{
			// Colors
			$colors  = "&colors[]=".urlencode("#3366cc");
			$colors .= "&colors[]=".urlencode("#109618");
			$colors .= "&colors[]=".urlencode("#dc3912");
			// Legends
			$legends = null;
			foreach($data as $line)
			{
				if(!isset($line['type'])) {
					$line['type'] = 'Sans Type';
				}
				$legends .= "&legends[]=".urlencode($line['type']);
			}
			$static = "?mod=charts&act=pie&xvtrs=none&width=500&height=350&colors=$colors&legends=$legends";
			
			// Build Graphs 1 (Dispatch By Amount Year n
			foreach($data as $line)
			{
				if(isset($line['rate_n'])) {
					$graphData1[] =  $line['rate_n'];
					$graphData2[] = $line['amount_rate_n'];
				}
				
				
				if($inputs['showym1']) {
					$graphData3[] = $line['rate_nm1'];
					$graphData4[] = $line['amount_rate_nm1'];
				}
			}
			
			$graphData1 = json_encode($graphData1);
			$graphData2 = json_encode($graphData2);
			
			$output .= "<div>";
			$title = urlencode("R&eacute;partition en Montant ".$labelY);
			$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData1&title=$title\" >";
			$title = urlencode("R&eacute;partitin en Nombre ".$labelY);
			$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData2&title=$title\" >";
			$output .= "</div>";
			if($inputs['showym1']) {
				$output .= "<br><div>";
				$graphData3 = json_encode($graphData3);
				$graphData4 = json_encode($graphData4);
				$title = urlencode("R&eacute;partition en Montant ".$labelYm1);
				$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData3&title=$title\" >";
				$title = urlencode("R&eacute;partition en Nombre".$labelYm1);
				$output .= "<img style=\"padding:5px 5px\" src=\"$static&data=$graphData4&title=$title\" >";
				$output .= "</div>";
			}
		} elseif($inputs['output_type'] == 0 || $inputs['output_type'] == Radio::ALL ) {
			Fox\Message::Warning("Vous ne pouvez pas afficher les graphiques lorsque l'affichage du d&eacute;tail est s&eacute;lectionn&eacute;.");
		}
	}
}
echo $screen->display();
if(isset($output))
	echo $output;